package com.cinema.dao.imp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cinema.Bean.MsgStatistical;
import com.cinema.Bean.User;
import com.cinema.dao.BaseDao;
import com.cinema.dao.IUserDao;
import com.cinema.util.DateUtil;
import com.mysql.jdbc.PreparedStatement;

/**
 * 用户表的mysql数据库实现类
 **/

public class UserDaoMysql extends BaseDao implements IUserDao {
	public int insert(User u) {
		String sql = "insert into user values(?,?,?,?,?,?,?,?)";
		String[] args = { u.getUserId(), u.getUserName(), u.getUserPwd(), u.getSex(), u.getEmail(),
				u.getMobile(), u.getBalance()+"",u.getRegisterTime()};
		return change(sql, args);
	}

	public int delete(User u) {
		String sql = "delete from user where userId=?";
		String[] args = { u.getUserId() };
		return change(sql, args);
	}

	public int update(User u) {
		String sql = "update user set userName=?,userPwd=?,sex=?,email=?,mobile=?,balance=? where userId=?";
		String[] args = { u.getUserName(), u.getUserPwd(), u.getSex(), u.getEmail(), u.getMobile(), u.getBalance()+"",
				u.getUserId() };
		return change(sql, args);
	}
	
	/**
	 * 根据用户id修改用户余额
	 */
	public int updateByUserId(User u) {
		String sql = "update user set balance=? where userId=?";
		String[] args = { u.getBalance()+"", u.getUserId() };
		return change(sql, args);
	}

	public User SelectById(String id) {
		// TODO Auto-generated method stub
		User u = null;
		String sql = "select * from user where userId=?";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				u = new User();
				u.setUserId(rs.getString("userId"));
				u.setUserName(rs.getString("userName"));
				u.setUserPwd(rs.getString("userPwd"));
				u.setSex(rs.getString("sex"));
				u.setEmail(rs.getString("email"));
				u.setMobile(rs.getString("mobile"));
				u.setBalance(rs.getDouble("balance"));
				u.setRegisterTime(DateUtil.fomat("yyyy-MM-dd",rs.getDate("registerTime")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
	}
	
	public List<User> selectAll() {
		// TODO Auto-generated method stub
		List<User> list = new ArrayList<User>();
		User u = null;
		String sql = "select * from User";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				u = new User();
				u.setUserId(rs.getString("userId"));
				u.setUserName(rs.getString("userName"));
				u.setUserPwd(rs.getString("userPwd"));
				u.setSex(rs.getString("sex"));
				u.setEmail(rs.getString("email"));
				u.setMobile(rs.getString("mobile"));
				u.setBalance(rs.getDouble("balance"));
				u.setRegisterTime(rs.getString("registerTime"));
				list.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取所有用户以及注册时间
	 */
	public List<MsgStatistical> selectByStatWhere() {
		// TODO Auto-generated method stub
		MsgStatistical m = null;
		List<MsgStatistical> list = new ArrayList<MsgStatistical>();
		String sql = "select \r\n" + 
				"	COUNT(u.userId) AS \"countUser\",\r\n" + 
				"	date_format(u.registerTime, '%Y-%m-%d' ) as \"registerTime\"\r\n" + 
				"from user as u\r\n" + 
				"GROUP BY date_format(u.registerTime, '%Y-%m-%d' )\r\n" + 
				"ORDER BY u.registerTime\r\n" + 
				"";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				m = new MsgStatistical();
				m.setCount(rs.getString("countUser"));
				m.setTime(rs.getString("registerTime"));
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	

	/**
	 * 根据关键词进行查询
	 */
	public List<User> Select(String keyword) {
		// TODO Auto-generated method stub
		List<User> list = new ArrayList<User>();
		User u = null;
		String sql = "select * from user where userId like '%" + keyword + "%' or userName like '%" + keyword
				+ "%' or mobile like '%" + keyword + "%'";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				u = new User();
				u.setUserId(rs.getString("userId"));
				u.setUserName(rs.getString("userName"));
				u.setUserPwd(rs.getString("userPwd"));
				u.setSex(rs.getString("sex"));
				u.setEmail(rs.getString("email"));
				u.setMobile(rs.getString("mobile"));
				u.setBalance(rs.getDouble("balance"));
				u.setRegisterTime(rs.getString("registerTime"));
				list.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 修改用户密码
	 */
	public int updatePwdByUserId(User u) {
		// TODO Auto-generated method stub
		String sql = "update user set userPwd=? where userId=?";
		String[] args = { u.getUserPwd(),u.getUserId() };
		return change(sql, args);
	}
}
